package com.vimi8.ebb.util;

import com.vimi8.ebb.model.TrainInfo;

import com.vimi8.ebb.model.TrainMember;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.Boolean;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

//import java.text.ParseException;
//import jxl.CellView;
//import jxl.format.BorderLineStyle;
//import javax.servlet.ServletOutputStream;

//import static java.io.FileDescriptor.out;
//import static javafx.scene.input.KeyCode.T;


public class JxlExcelUtil {
    private static Logger logger = LoggerFactory.getLogger(JxlExcelUtil.class);
  public static Map<String,String> geoMap=new HashMap<String,String>();

    static {
        geoMap.put("碧江区","520602000000");
        geoMap.put("万山区","520603000000");
        geoMap.put("江口县","520621000000");
        geoMap.put("玉屏侗族自治县","520622000000");
        geoMap.put("石阡县","520623000000");
        geoMap.put("思南县","520624000000");
        geoMap.put("印江土家族苗族自治县","520625000000");
        geoMap.put("德江县","520626000000");
        geoMap.put("沿河土家族自治县","520627000000");
        geoMap.put("松桃苗族自治县","520600000000");
        geoMap.put("高新区","520604000000");
        geoMap.put("大龙经济开发区","520605000000");

    }
    //读取培训信息
    public static List readTrainInfoExcel(MultipartFile multipartFile) {
        // List<PovertyHousehold> poorFamilyList=(List<PovertyHousehold> poorFamilyList)obj;
        List<TrainInfo> list = new ArrayList<TrainInfo>();
        TrainInfo trainInfo = null;
        try {
            //打开文件
            Workbook book = Workbook.getWorkbook(multipartFile.getInputStream());
            //取得第一个sheet
            Sheet sheet = book.getSheet(0);

            //取得行数
            int rows = sheet.getRows();
            logger.info("rows==" + rows);

            for (int i = 1; i < rows; i++) {
                Cell[] cell = sheet.getRow(i);
                trainInfo = new TrainInfo();
                trainInfo.setId(UUID.randomUUID().toString());
                String trainAddress = sheet.getCell(0, i).getContents();
                trainInfo.setTrainAddress(trainAddress);

                String addressCode = sheet.getCell(1, i).getContents();
                if(!StringUtils.isEmpty(addressCode)){
                    addressCode=geoMap.get(addressCode.trim());
                }
                trainInfo.setTrainAddressCode(addressCode);
                String trainTeacher=sheet.getCell(2,i).getContents();
              trainInfo.setTrainTeacher(trainTeacher);
//sex
                String sex = sheet.getCell(3, i).getContents();
                trainInfo.setSex(sex);
                String phone = sheet.getCell(4, i).getContents();
                trainInfo.setPhone(phone);

                //organization
                String trainObject = sheet.getCell(5, i).getContents();
                trainInfo.setTrainObject(trainObject);
                String trainDate = sheet.getCell(6, i).getContents();
                trainInfo.setTrainDate(trainDate);


                String trainTitle = sheet.getCell(7, i).getContents();
                trainInfo.setTrainTitle(trainTitle);
                String trainContent = sheet.getCell(8, i).getContents();
                trainInfo.setTrainContent(trainContent);

                //job
                String trainType = sheet.getCell(9, i).getContents();
                trainInfo.setTrainType(trainType);
                String train_evaluate = sheet.getCell(10, i).getContents();
                trainInfo.setTrainEvaluate(train_evaluate);


                //subordination
                String train_peoples = sheet.getCell(11, i).getContents();
                trainInfo.setTrainPeoples(train_peoples);
                if(!StringUtils.isEmpty(trainInfo.getTrainTitle())){
                    list.add(trainInfo);
                   // logger.info("datalist==" + list.toString());
                }

            }
            //关闭文件
            book.close();
        } catch (BiffException e) {
            logger.error("数据导入错误！", e);
        } catch (IOException e) {
            logger.error("数据入出错误！", e);
        }
        return list;
    }

    //读取培训信息
    public static List readTrainMmberExcel(MultipartFile multipartFile,String trainId) {
        // List<PovertyHousehold> poorFamilyList=(List<PovertyHousehold> poorFamilyList)obj;
        List<TrainMember> list = new ArrayList<TrainMember>();
        TrainMember trainMember = null;
        try {
            //打开文件
            Workbook book = Workbook.getWorkbook(multipartFile.getInputStream());
            //取得第一个sheet
            Sheet sheet = book.getSheet(0);

            //取得行数
            int rows = sheet.getRows();
            logger.info("rows==" + rows);

            for (int i = 1; i < rows; i++) {
                Cell[] cell = sheet.getRow(i);
                trainMember = new TrainMember();
                trainMember.setId(UUID.randomUUID().toString());



                String memberName = sheet.getCell(0, i).getContents();
                trainMember.setMemberName(memberName);

                String sex = sheet.getCell(1, i).getContents();
                trainMember.setMemberSex(sex);

                String phone=sheet.getCell(2,i).getContents();
               trainMember.setMemberPhone(phone);
//sex
                String memberType = sheet.getCell(3, i).getContents();
               trainMember.setMemberType(memberType);
                String trainEvaualte = sheet.getCell(4, i).getContents();
              trainMember.setTrainEvaluate(trainEvaualte);
                //organization
                String enterOrientation = sheet.getCell(5, i).getContents();
                trainMember.setEnterOrientation(enterOrientation);

                String address = sheet.getCell(6, i).getContents();
               trainMember.setMemberAddress(address);

            //培训标题作为 关联培训信息
                String    trainTitle = sheet.getCell(7, i).getContents();
                if(StringUtils.isEmpty(trainTitle)){
                    trainMember.setTrainId(trainId);
                }else{
                    trainMember.setTrainId(trainTitle);
                }


                if(!StringUtils.isEmpty(trainMember.getTrainId())){
                    list.add(trainMember);
                   // logger.info("datalist==" + list.toString());
                }

            }
            //关闭文件
            book.close();
        } catch (BiffException e) {
            logger.error("数据导出错误！", e);
        } catch (IOException e) {
            logger.error("数据导出错误！", e);
        }
        return list;
    }

    /**
     * @param headers  excel表头
     * @param methods  数据源对象提取数据的方法与表头一一对应
     * @param dataList 数据源
     * @throws Exception
     */
    public static void writeExcelObject(String filePath, String[] headers, String[] methods, List dataList,HttpServletResponse response)
            throws Exception {
        WritableWorkbook wwb = null;
       // ByteArrayOutputStream bos = new ByteArrayOutputStream();

        byte[] bRet = null;
        try {
            // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
            wwb = Workbook.createWorkbook(new FileOutputStream(new File(filePath)));
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
        if (wwb != null) {
            // 创建一个可写入的工作表
            // Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
            WritableSheet ws = wwb.createSheet("Sheet0", 0);
            for (int i = 0; i < headers.length; i++) {
                // 写入头信息
                WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

                WritableCellFormat wcfFC = new WritableCellFormat(wf);
                // wcfFC.setBackground(jxl.format.Colour.RED);
                Label labelC = new Label(i, 0, headers[i], wcfFC);
                ws.addCell(labelC);
            }
            for (int i = 0; i < dataList.size(); i++) {

                for (int j = 0; j < methods.length; j++) {

                    // 写入数据信息
                    Object obj = dataList.get(i);

                    Method method = obj.getClass().getMethod(methods[j]);
                    if (method.invoke(obj) instanceof Date) {
                        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                        String date = df.format(method.invoke(obj));
                        Label labelC = new Label(j, i + 1, date);
                        ws.addCell(labelC);
                    } else if (method.invoke(obj) instanceof Number) {
                        NumberFormat nf = new NumberFormat("#.#######");
                        WritableCellFormat wcfN = new WritableCellFormat(nf);
                        Number labelNF = new Number(j, i + 1, new Double(method
                                .invoke(obj).toString()), wcfN);
                        ws.addCell(labelNF);
                    }else if (method.invoke(obj) instanceof Boolean) {
                        Boolean b= (Boolean) method.invoke(obj);
                        //jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
                        Label labelC = new Label(j, i + 1, b==true?"是":"否");
                        ws.addCell(labelC);
                    } else {
                        Label labelC = new Label(j, i + 1, method.invoke(obj) != null ? method
                                .invoke(obj).toString() : "");
                      //  logger.info("label value="+labelC.getContents());
                        ws.addCell(labelC);
                    }
                }

            }

            try {
                // 从内存中写入文件中
                wwb.write();
                // 关闭资源，释放内存
                wwb.close();
                //bRet = bos.toByteArray();

            } catch (IOException e) {
                if (logger.isErrorEnabled())
                    logger.error("数据导出错误！", e);
             //   return null;

            }
          //  return bos;
        }

}
private static boolean booleanFormat(String booleanStr){
    if(booleanStr==null||"".equals(booleanStr)){
        return false;
    }
    booleanStr=booleanStr.trim();
    if(booleanStr.equals("是")){
        return true;
    }
    return false;
}
    //把区级的帮扶人信息合并为镇级信息
private static String getTownStringFromDistrict(String districtStr,String townStr){
    String townString=null;
    if(null==townStr||"".equals(townStr)){
         return (null==districtStr||"".equals(districtStr))?"":"("+districtStr+")";
    }
    if(null==districtStr||"".equals(districtStr)){
        return (null==townStr||"".equals(townStr))?"":townStr;
    }
    return townStr+"("+districtStr+")";
}

}
